-- psql projet_4ir
-- psql -f functionsSecretaire.sql projet_4ir
-- \q pour quitter

DROP FUNCTION IF EXISTS getInformationsSecretaire( VARCHAR(50) );
CREATE OR REPLACE FUNCTION getInformationsSecretaire( mail VARCHAR(50) ) RETURNS TABLE(email VARCHAR(50), affectation VARCHAR(10), identifiant VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30)) AS $$
BEGIN
	RETURN QUERY SELECT S.email, D.libelle affectation, CAST(S.id_secretaire AS VARCHAR(20)), S.nom, S.prenom FROM Secretaire S, Departement D WHERE S.email = mail AND S.departement = D.id_departement;	
END;
$$ LANGUAGE plpgsql;

--recupere la liste des etudiants qui ont plus de 3 absences non-justifiees dans les cours
DROP FUNCTION IF EXISTS getMoreThanXNotJustified(INT);
CREATE OR REPLACE FUNCTION getMoreThanXNotJustified(X INT) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite, nb_absences BIGINT ) AS $$
BEGIN
	RETURN QUERY SELECT F.ine, E.nom, E.prenom, E.affectation, F.count nb_absences FROM (SELECT A.ine, COUNT(*) FROM Absence A WHERE A.credibilite = 0 GROUP BY A.ine) AS F  INNER JOIN Etudiant E ON E.ine = F.ine WHERE F.count > X;
END;
$$ LANGUAGE plpgsql;

--recupere la liste des etudiants qui n'ont jamais été absents pour toutes les matières
DROP FUNCTION IF EXISTS getNeverAbsent();
CREATE OR REPLACE FUNCTION getNeverAbsent() RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite) AS $$
BEGIN
	RETURN QUERY SELECT E.ine, E.nom, E.prenom, E.affectation FROM Etudiant E WHERE E.ine NOT IN (SELECT DISTINCT A.ine FROM Absence A);
END;
$$ LANGUAGE plpgsql;

--récupère la liste des étudiants qui  ont été toujours absents pour toutes les matières
DROP FUNCTION IF EXISTS getAlwaysAbsentForEverything();
CREATE OR REPLACE FUNCTION getAlwaysAbsentForEverything() RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), matiere VARCHAR(50) ) AS $$
BEGIN
	RETURN QUERY SELECT Q.ine, Q.nom, Q.prenom, Mat.nom matiere FROM (SELECT A.ine, E.nom, E.prenom, A.matiere, COUNT(*) total_absences FROM Absence A, Seance S, Etudiant E WHERE E.ine = A.ine GROUP BY A.ine, E.nom, E.prenom, A.matiere) AS Q INNER JOIN Matiere Mat ON Mat.id_matiere = Q.matiere WHERE Q.total_absences = (Mat.nb_heures_Cours + Mat.nb_heures_Tds + Mat.nb_heures_Tps);
END;
$$ LANGUAGE plpgsql;

--récupère la liste des étudiants qui ont moins X absences non justifiées dans au moins un des cours
DROP FUNCTION IF EXISTS getAtLeastXNotJustifiedInOneCourse(INT);
CREATE OR REPLACE FUNCTION getAtLeastXNotJustifiedInOneCourse(X INT) RETURNS TABLE(ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite, matiere VARCHAR(50), nb_abscences BIGINT) AS $$
BEGIN
	RETURN QUERY SELECT Q.ine, E.prenom, E.nom, E.affectation, Q.matiere, Q.nb_absences FROM (SELECT A.ine, Mat.nom matiere, COUNT(*) nb_absences FROM Absence A, Matiere Mat WHERE Mat.id_matiere = A.matiere AND A.credibilite = 0 GROUP BY A.ine, Mat.nom) AS Q  INNER JOIN Etudiant E ON E.ine = Q.ine WHERE Q.nb_absences >= X;
END;
$$ LANGUAGE plpgsql;

--affiche l'etat des absences pour un etudiant particulier
DROP FUNCTION IF EXISTS getAbsencesFor( VARCHAR(20) );
CREATE OR REPLACE FUNCTION getAbsencesFor(param_ine VARCHAR(20)) RETURNS TABLE( id_seance INT, id_matiere INT, isA Type_Seance,  matiere VARCHAR(50), nom_enseignant VARCHAR(30), prenom_enseignant VARCHAR(30), justification VARCHAR(300) , credibilite INT, heure TIMESTAMP ) AS $$
BEGIN
	RETURN QUERY SELECT A.id_seance, A.matiere, A.isA, Matiere.nom matiere, E.nom nom_enseignant, E.prenom prenom_enseignant, A.justification, A.credibilite, date_trunc('minute', S.heure) heure FROM Seance S, Absence A, Matiere, Enseignant E WHERE E.immatriculation = S.enseignant AND S.id_seance = A.id_seance AND S.matiere = A.matiere AND S.isA = A.isA AND Matiere.id_matiere = A.matiere AND A.ine = param_ine;
END;	
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS changeAbsenceJustification(VARCHAR(20), INT, INT, Type_Seance, VARCHAR(300), INT);
CREATE OR REPLACE FUNCTION changeAbsenceJustification(param_ine VARCHAR(20), param_id_seance INT, param_matiere INT, param_typeSeance Type_Seance, param_justification VARCHAR(300), param_credibilite INT) RETURNS VOID AS $$
BEGIN
	UPDATE Absence SET justification = param_justification, credibilite = param_credibilite WHERE id_seance = param_id_seance AND ine = param_ine AND param_matiere = matiere AND param_typeSeance = isA;
END;	
$$ LANGUAGE plpgsql;

SELECT * FROM getInformationsSecretaire('l.filiel@gmail.com');
SELECT * FROM getInformationsSecretaire('h.turdar@gmail.com');

SELECT * FROM getAtLeastXNotJustifiedInOneCourse(2);
SELECT * FROM getAlwaysAbsentForEverything();
SELECT * FROM getNeverAbsent();
SELECT * FROM getAbsencesFor('12AR78BDC961');
SELECT * FROM getMoreThanXNotJustified(2);